﻿Public Class Data

#Region "Tables"

#Region "TableSingle"

    Enum TableSingleType
        TOPICS
        ACTIVITIES
        BACKLOGS
    End Enum

    Public Structure TableSingleInfo
        Public strNameForm As String
        Public strNameTable As String
        Public strNameElement As String
        Public strNameElementComplete As String

        Private m_intType As TableSingleType
        Public Property intType As TableSingleType
            Get
                Return m_intType
            End Get
            Set(ByVal value As TableSingleType)
                m_intType = value
                Select Case value
                    Case TableSingleType.TOPICS
                        strNameForm = "Topics"
                        strNameElement = "Topics"
                        strNameElementComplete = "Topics"
                        strNameTable = "Topics"
                    Case TableSingleType.ACTIVITIES
                        strNameForm = "Activities"
                        strNameElement = "Activities"
                        strNameElementComplete = "Activities"
                        strNameTable = "Activities"
                    Case TableSingleType.BACKLOGS
                        strNameForm = "Backlogs"
                        strNameElement = "Backlogs"
                        strNameElementComplete = "Backlogs"
                        strNameTable = "Backlogs"
                End Select
            End Set
        End Property
    End Structure

    Public Class TableSingle
        Public mID As Int32
        Public mName As String
        Public strError As String = ""
        Public strNameTable As String
        Public strNameElementComplete As String

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter(String.Format("Select * From {0} Where ID={1}", strNameTable, intID), gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable(strNameTable)
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mName = dtTable.Rows(0)("Name").ToString
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer, Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                End If

                Using cmCommand As New MySqlCommand(String.Format("Delete From {0} Where ID={1}", strNameTable, inID), cnConnection)
                    cmCommand.Transaction = inTrans
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                If inTrans IsNot Nothing Then inTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox(String.Format("Do you want to eliminate {0}?", strNameElementComplete), vbQuestion + vbYesNo + vbDefaultButton2, "Deletezione") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                End If

                ' TODO: RV - to modify slq
                Dim strValues As String = String.Format("'{0}'", mName.Replace("'", "''"))
                Dim strGetID As String = String.Format("SELECT @@IDENTITY ")
                Dim strSql As String = String.Format("Insert Into {1} " &
                  "(Name) " &
                  "Values ({0}); {2} ", strValues, strNameTable, "")

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.Transaction = inTrans
                    cmCommand.ExecuteScalar()
                End Using
                Using cmCommand As New MySqlCommand(strGetID, cnConnection)
                    cmCommand.Transaction = inTrans
                    Return Utility.ConvStrNum(cmCommand.ExecuteScalar().ToString)
                End Using

            Catch ex As Exception
                If inTrans IsNot Nothing Then inTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                End If

                Dim strSql As String = String.Format("" &
                 "Update {1} Set " &
                  "      Name='{0}' " &
                  "Where ID={2}", mName.Replace("'", "''"), strNameTable, mID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.Transaction = inTrans
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                If inTrans IsNot Nothing Then inTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Shared Function Exist(ByVal inName As String, ByVal inIDtoAvoid As Integer, ByVal inNameTable As String) As Boolean
            Try
                Dim strSql As String = String.Format("" &
                           "Select * From {2} " &
                           "Where Name='{0}' and Not ID={1}", inName, inIDtoAvoid, inNameTable)
                Using cnConnection As New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    Using cmCommand As New MySqlCommand(strSql, cnConnection)
                        Return cmCommand.ExecuteReader.Read
                    End Using
                End Using

            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Shared Function LoadDataTable(ByVal inNameTable As String) As DataTable
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From " & inNameTable, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable(inNameTable)
                        daAdapt.Fill(dtTable)
                        Return dtTable.Copy
                    End Using
                End Using
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

    End Class

#End Region

    Public Class TProjects
        Public mID As System.Int32
        Public mName As String = ""
        Public mDescription As String = ""
        Public mStartDate As String = ""
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From Projects Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Projects")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mName = dtTable.Rows(0)("Name").ToString
                            mDescription = dtTable.Rows(0)("Description").ToString
                            mStartDate = dtTable.Rows(0)("StartDate").ToString
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                                                  "Delete       " &
                                                  "From        Projects " &
                                                  "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox("Do you want to eliminate the project?", vbQuestion + vbYesNo + vbDefaultButton2, "Deletete") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Projects Limit 0", cnConnection)
                    Using dtTable As New DataTable("Projects")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("Name") = mName
                        lNewRow("Description") = mDescription
                        lNewRow("StartDate") = If(mStartDate <> "", Utility.FormatSqlData(mStartDate), DBNull.Value)
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Projects Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("Projects")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("Name") = mName
                            lRow("Description") = mDescription
                            lRow("StartDate") = If(mStartDate <> "", Utility.FormatSqlData(mStartDate), DBNull.Value)
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

    End Class

    Public Class TSprints
        Public mID As System.Int32
        Public mName As String = ""
        Public mStartDate As String = ""
        Public mBacklogsID As System.Int32
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From Sprints Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Sprints")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mName = dtTable.Rows(0)("Name").ToString
                            mStartDate = dtTable.Rows(0)("StartDate").ToString
                            mBacklogsID = Utility.ConvStrNum(dtTable.Rows(0)("BacklogsID").ToString)
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                                                  "Delete       " &
                                                  "From        Sprints " &
                                                  "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox("Do you want to eliminate the sprint?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Sprints Limit 0", cnConnection)
                    Using dtTable As New DataTable("Sprints")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("Name") = mName
                        lNewRow("StartDate") = If(mStartDate <> "", Utility.FormatSqlData(mStartDate), DBNull.Value)
                        lNewRow("BacklogsID") = mBacklogsID
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Sprints Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("Sprints")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("Name") = mName
                            lRow("StartDate") = If(mStartDate <> "", Utility.FormatSqlData(mStartDate), DBNull.Value)
                            lRow("BacklogsID") = mBacklogsID
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

    End Class

    Public Class TMeetings
        Public mID As System.Int32
        Public mName As String = ""
        Public mDescription As String = ""
        Public mDate As String = ""
        Public mTime As String = ""
        Public mTopicsID As System.Int32
        Public mUserStoriesID As System.Int32
        Public mMinute As System.String
        Public mStatus As System.SByte
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From Meetings Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Meetings")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mName = dtTable.Rows(0)("Name").ToString
                            mDescription = dtTable.Rows(0)("Description").ToString
                            mDate = CType(dtTable.Rows(0)("Date").ToString, DateTime)
                            mTime = dtTable.Rows(0)("Time").ToString
                            mTopicsID = Utility.ConvStrNum(dtTable.Rows(0)("TopicsID").ToString)
                            mUserStoriesID = Utility.ConvStrNum(dtTable.Rows(0)("UserStoriesID").ToString)
                            mMinute = dtTable.Rows(0)("Minute").ToString
                            mStatus = Utility.ConvStrNum(dtTable.Rows(0)("Status").ToString)
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                          "Delete       " &
                          "From        Meetings " &
                          "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox("Do you want to delete meeting?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Meetings Limit 0", cnConnection)
                    Using dtTable As New DataTable("Meetings")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("TopicsID") = If(mTopicsID > 0, mTopicsID, DBNull.Value)
                        lNewRow("UserStoriesID") = If(mUserStoriesID > 0, mUserStoriesID, DBNull.Value)
                        lNewRow("Description") = mDescription
                        lNewRow("Name") = mName
                        lNewRow("Date") = If(mDate <> "", Utility.FormatSqlData(mDate), DBNull.Value)
                        lNewRow("Time") = mTime
                        lNewRow("Minute") = mMinute
                        lNewRow("Status") = mStatus
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Meetings Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("Meetings")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("TopicsID") = If(mTopicsID > 0, mTopicsID, DBNull.Value)
                            lRow("UserStoriesID") = If(mUserStoriesID > 0, mUserStoriesID, DBNull.Value)
                            lRow("Description") = mDescription
                            lRow("Name") = mName
                            lRow("Date") = If(mDate <> "", Utility.FormatSqlData(mDate), DBNull.Value)
                            lRow("Time") = mTime
                            lRow("Minute") = mMinute
                            lRow("Status") = mStatus
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Shared Function GetMeetingID(ByVal InDate As Date, ByVal InTime As String) As Integer
            Try
                Dim strSql As String = String.Format("" &
                                                    " Select     ID " &
                                                    " From       Meetings " &
                                                    " Where      Date = '{0}' and " &
                                                    "            Time = '{1}' ", Utility.FormatSqlData(InDate), InTime)

                Using daAdapt As New MySqlDataAdapter(strSql, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Meetings")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Return Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                        Else
                            Return 0
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Return 0
            End Try
        End Function

    End Class

    Public Class TUserStories
        Public mID As System.Int32
        Public mNumber As String = ""
        Public mName As String = ""
        Public mDescription As String = ""
        Public mDone As String = ""
        Public mPriority As System.Int32
        Public mSprintsID As System.Int32
        Public mProjectsID As System.Int32
        Public mStatus As System.SByte
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From UserStories Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("UserStories")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mNumber = dtTable.Rows(0)("Number").ToString
                            mName = dtTable.Rows(0)("Name").ToString
                            mDescription = dtTable.Rows(0)("Description").ToString
                            mDone = dtTable.Rows(0)("Done").ToString
                            mPriority = Utility.ConvStrNum(dtTable.Rows(0)("Priority").ToString)
                            mSprintsID = Utility.ConvStrNum(dtTable.Rows(0)("SprintsID").ToString)
                            mProjectsID = Utility.ConvStrNum(dtTable.Rows(0)("ProjectsID").ToString)
                            mStatus = Utility.ConvStrNum(dtTable.Rows(0)("Status").ToString)
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                          "Delete       " &
                          "From        UserStories " &
                          "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox("Do you want to delete user story?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From UserStories Limit 0", cnConnection)
                    Using dtTable As New DataTable("UserStories")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("Number") = mNumber
                        lNewRow("Name") = mName
                        lNewRow("Description") = mDescription
                        lNewRow("Done") = mDone
                        lNewRow("Priority") = mPriority
                        lNewRow("SprintsID") = If(mSprintsID > 0, mSprintsID, DBNull.Value)
                        lNewRow("ProjectsID") = If(mProjectsID > 0, mProjectsID, DBNull.Value)
                        lNewRow("Status") = mStatus
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From UserStories Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("UserStories")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("Number") = mNumber
                            lRow("Name") = mName
                            lRow("Description") = mDescription
                            lRow("Done") = mDone
                            lRow("Priority") = mPriority
                            lRow("SprintsID") = If(mSprintsID > 0, mSprintsID, DBNull.Value)
                            lRow("ProjectsID") = If(mProjectsID > 0, mProjectsID, DBNull.Value)
                            lRow("Status") = mStatus
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

    End Class

    Public Class TTasks
        Public mID As System.Int32
        Public mOrder As System.Int32
        Public mDescription As System.String
        Public mActivitiesID As System.Int32
        Public mUserStoriesID As System.Int32
        Public mExpectedHours As System.Int32
        Public mStatus As System.SByte
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From Tasks Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Tasks")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mOrder = Utility.ConvStrNum(dtTable.Rows(0)("Order").ToString)
                            mDescription = dtTable.Rows(0)("Description").ToString
                            mActivitiesID = Utility.ConvStrNum(dtTable.Rows(0)("ActivitiesID").ToString)
                            mUserStoriesID = Utility.ConvStrNum(dtTable.Rows(0)("UserStoriesID").ToString)
                            mExpectedHours = Utility.ConvStrNum(dtTable.Rows(0)("ExpectedHours").ToString)
                            mStatus = Utility.ConvStrNum(dtTable.Rows(0)("Status").ToString)
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                          "Delete       " &
                          "From        Tasks " &
                          "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function IsPossibleDelete() As Boolean
            Try
                If MsgBox("Do you want to delete task?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete") = vbYes Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception
                strError = ex.message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Tasks Limit 0", cnConnection)
                    Using dtTable As New DataTable("Tasks")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("Order") = mOrder
                        lNewRow("Description") = mDescription
                        lNewRow("ActivitiesID") = If(mActivitiesID > 0, mActivitiesID, DBNull.Value)
                        lNewRow("UserStoriesID") = If(mUserStoriesID > 0, mUserStoriesID, DBNull.Value)
                        lNewRow("ExpectedHours") = mExpectedHours
                        lNewRow("Status") = mStatus
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From Tasks Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("Tasks")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("Order") = mOrder
                            lRow("Description") = mDescription
                            lRow("ActivitiesID") = If(mActivitiesID > 0, mActivitiesID, DBNull.Value)
                            lRow("UserStoriesID") = If(mUserStoriesID > 0, mUserStoriesID, DBNull.Value)
                            lRow("ExpectedHours") = mExpectedHours
                            lRow("Status") = mStatus
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

    End Class

    Public Class TTasksHours
        Public mID As System.Int32
        Public mTasksID As System.Int32
        Public mHours1 As System.SByte
        Public mHours2 As System.SByte
        Public mHours3 As System.SByte
        Public mHours4 As System.SByte
        Public mHours5 As System.SByte
        Public mHours6 As System.SByte
        Public mHours7 As System.SByte
        Public mHours8 As System.SByte
        Public mHours9 As System.SByte
        Public mHours10 As System.SByte
        Public mHours11 As System.SByte
        Public mHours12 As System.SByte
        Public strError As String = ""

        Public Function LoadData(ByVal intID As Integer) As Boolean
            Return LoadData("ID=" & intID)
        End Function

        Public Function LoadData(ByVal inWhere As String) As Boolean
            Try
                Using daAdapt As New MySqlDataAdapter("Select * From TasksHours Where " & inWhere, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("TasksHours")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then

                            mID = Utility.ConvStrNum(dtTable.Rows(0)("ID").ToString)
                            mTasksID = Utility.ConvStrNum(dtTable.Rows(0)("TasksID").ToString)
                            mHours1 = Utility.ConvStrNum(dtTable.Rows(0)("Hours1").ToString)
                            mHours2 = Utility.ConvStrNum(dtTable.Rows(0)("Hours2").ToString)
                            mHours3 = Utility.ConvStrNum(dtTable.Rows(0)("Hours3").ToString)
                            mHours4 = Utility.ConvStrNum(dtTable.Rows(0)("Hours4").ToString)
                            mHours5 = Utility.ConvStrNum(dtTable.Rows(0)("Hours5").ToString)
                            mHours6 = Utility.ConvStrNum(dtTable.Rows(0)("Hours6").ToString)
                            mHours7 = Utility.ConvStrNum(dtTable.Rows(0)("Hours7").ToString)
                            mHours8 = Utility.ConvStrNum(dtTable.Rows(0)("Hours8").ToString)
                            mHours9 = Utility.ConvStrNum(dtTable.Rows(0)("Hours9").ToString)
                            mHours10 = Utility.ConvStrNum(dtTable.Rows(0)("Hours10").ToString)
                            mHours11 = Utility.ConvStrNum(dtTable.Rows(0)("Hours11").ToString)
                            mHours12 = Utility.ConvStrNum(dtTable.Rows(0)("Hours12").ToString)
                            Return True
                        Else
                            Return False
                        End If
                    End Using
                End Using
            Catch ex As Exception
                strError = ex.message
                Return False
            End Try
        End Function

        Public Function Delete(ByVal inID As Integer) As Boolean
            Try
                Dim cnConnection As MySqlConnection
                cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                cnConnection.Open()

                Dim strSql As String = String.Format("" &
                          "Delete       " &
                          "From        Tasks " &
                          "Where       ID={0}", inID)

                Using cmCommand As New MySqlCommand(strSql, cnConnection)
                    cmCommand.ExecuteNonQuery()
                End Using

                Return True
            Catch ex As Exception
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Function Insert(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Integer
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From TasksHours Limit 0", cnConnection)
                    Using dtTable As New DataTable("TasksHours")
                        daAdapt.Fill(dtTable)
                        Dim lNewRow As DataRow = dtTable.NewRow
                        lNewRow("TaskID") = If(mTasksID > 0, mTasksID, DBNull.Value)
                        lNewRow("Hours1") = mHours1
                        lNewRow("Hours2") = mHours2
                        lNewRow("Hours3") = mHours3
                        lNewRow("Hours4") = mHours4
                        lNewRow("Hours5") = mHours5
                        lNewRow("Hours6") = mHours6
                        lNewRow("Hours7") = mHours7
                        lNewRow("Hours8") = mHours8
                        lNewRow("Hours9") = mHours9
                        lNewRow("Hours10") = mHours10
                        lNewRow("Hours11") = mHours11
                        lNewRow("Hours12") = mHours12
                        dtTable.Rows.Add(lNewRow)

                        Dim cmCommand As New MySqlCommandBuilder(daAdapt)
                        cmCommand.GetInsertCommand.Transaction = inTrans
                        daAdapt.InsertCommand = cmCommand.GetInsertCommand.Clone
                        daAdapt.InsertCommand.CommandText &= ";SELECT last_insert_id() as 'LastID'"
                        daAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
                        cmCommand.Dispose()
                        cmCommand = Nothing
                        daAdapt.Update(dtTable)

                        If inTrans Is Nothing Then trTrans.Commit()
                        Return Utility.ConvStrNum(dtTable.Rows(0)("LastID").ToString)

                    End Using
                End Using
            Catch ex As Exception
                strError = ex.Message
                If trTrans IsNot Nothing Then trTrans.Rollback()
                Return 0
            End Try
        End Function

        Public Function Modify(Optional ByRef inCn As MySqlConnection = Nothing, Optional ByRef inTrans As MySqlTransaction = Nothing) As Boolean
            Dim trTrans As MySqlTransaction = Nothing
            Try
                Dim cnConnection As MySqlConnection
                If inCn IsNot Nothing Then
                    cnConnection = inCn
                    trTrans = inTrans
                Else
                    cnConnection = New MySqlConnection(gConnectionStringAgenda.ToString)
                    cnConnection.Open()
                    trTrans = cnConnection.BeginTransaction
                End If

                Using daAdapt As New MySqlDataAdapter("Select * From TasksHours Where ID=" & mID, cnConnection)
                    Using dtTable As New DataTable("TasksHours")
                        daAdapt.Fill(dtTable)
                        If dtTable.Rows.Count > 0 Then
                            Dim lRow As DataRow = dtTable.Rows(0)
                            lRow("Hours1") = mHours1
                            lRow("Hours2") = mHours2
                            lRow("Hours3") = mHours3
                            lRow("Hours4") = mHours4
                            lRow("Hours5") = mHours5
                            lRow("Hours6") = mHours6
                            lRow("Hours7") = mHours7
                            lRow("Hours8") = mHours8
                            lRow("Hours9") = mHours9
                            lRow("Hours10") = mHours10
                            lRow("Hours11") = mHours11
                            lRow("Hours12") = mHours12
                        Else
                            Return False
                        End If

                        Using cmCommand As New MySqlCommandBuilder(daAdapt)
                            cmCommand.GetInsertCommand.Transaction = trTrans
                            daAdapt.Update(dtTable)
                        End Using
                    End Using
                End Using

                If inTrans Is Nothing Then trTrans.Commit()

                Return True
            Catch ex As Exception
                If trTrans IsNot Nothing Then trTrans.Rollback()
                strError = ex.Message
                Return False
            End Try
        End Function

        Public Shared Function GetDTTasksHours(ByVal inSprintsID As Integer) As DataTable
            Try
                Dim strSql As String = ""

                strSql = String.Format("Select      Tasks.ID, " & _
                                       "            concat(UserStories.Number,' - ',UserStories.Name) as 'US', " & _
                                       "            Tasks.Description as 'Task', " & _
                                       "            Activities.Name as 'Activity', " & _
                                       "            CASE Tasks.Status WHEN 1 THEN 'Planned' WHEN 2 THEN 'OnGoing' WHEN 3 THEN 'OnHold' WHEN 4 THEN 'Done' WHEN 5 THEN 'Cancelled' WHEN 6 THEN 'Next' ELSE '' END as 'Status', " &
                                       "            Tasks.ExpectedHours as 'Exp Hours', " & _
                                       "            TasksHours.Hours1 as '1', " & _
                                       "            TasksHours.Hours2 as '2', " & _
                                       "            TasksHours.Hours3 as '3', " & _
                                       "            TasksHours.Hours4 as '4', " & _
                                       "            TasksHours.Hours5 as '5', " & _
                                       "            TasksHours.Hours6 as '6', " & _
                                       "            TasksHours.Hours7 as '7', " & _
                                       "            TasksHours.Hours8 as '8', " & _
                                       "            TasksHours.Hours9 as '9', " & _
                                       "            TasksHours.Hours10 as '10', " & _
                                       "            TasksHours.Hours11 as '11', " & _
                                       "            TasksHours.Hours12 as '12' " & _
                                       "From        Tasks " & _
                                       "            left Join TasksHours on Tasks.ID=TasksHours.TasksID " & _
                                       "            left Join UserStories on Tasks.UserStoriesID=UserStories.ID " & _
                                       "            left Join Activities on Tasks.ActivitiesID=Activities.ID " & _
                                       "Where       SprintsID = {0} " & _
                                       "Order by    Tasks.Order ", inSprintsID)

                Using daAdapt As New MySqlDataAdapter(strSql, gConnectionStringAgenda.ToString)
                    Using dtTable As New DataTable("Tasks")
                        daAdapt.Fill(dtTable)
                        Return dtTable.Copy
                    End Using
                End Using
            Catch ex As Exception
                Return Nothing
            End Try
        End Function

    End Class

#End Region

End Class
